﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Trade.Model;

namespace Trade.DAL
{
    public class Employee
    {
        public const string SQL_SELECT_EMPLOYEE = "Select EmployeeNo, ChineseName, EnglishName, Title, Gender, Tel, Mobile, Address, ZipCode, IdCardNo, HireDate, Department, LoginId, RecordId from Employee";
        public const string SQL_WHERE = " where 1 > 2";
        public const string SQL_WHERE_RECORDID = " where RecordId = @RecordId";

        //读取所有的客户资料列表到DataTable中
        public DataTable GetEmployeeList()
        {
            return SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_EMPLOYEE, null);
        }

        //创建数据表结构
        public DataTable EmployeeTemplate()
        {
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_EMPLOYEE + SQL_WHERE, null))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //根据指定的记录编号读取相应的员工资料
        public DataRow GetEmployeeByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            DataTable dt = SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_EMPLOYEE + SQL_WHERE_RECORDID, parm);
            DataRow dr = dt.Rows[0];
            return dr;
        }

        public EmployeeInfo GetEmployessInfoByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            EmployeeInfo employeeInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_EMPLOYEE + SQL_WHERE_RECORDID, parm))
            {
                while (dr.Read())
                {
                    employeeInfo = new EmployeeInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7),
                                                                dr.GetString(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12), dr.GetInt32(13));
                }
            }
            return employeeInfo;
        }

        //检测员工编号的唯一性
        public bool IsUniqueEmployeeNo(string employeeNo, int recordId)
        {
            string sqlSelect = "Select count(EmployeeNo) from Employee where ltrim(rtrim(EmployeeNo)) = @EmployeeNo and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@EmployeeNo", SqlDbType.VarChar, 8),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = employeeNo.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        //将数据更新到数据库
        public void WriteToDatabase(string action, EmployeeInfo employee, out int recordId)
        {
            string sql = "Exec Usp_ModifyEmployee @p_Action, @p_EmployeeNo, @p_ChineseName, @p_EnglishName, @p_Title, @p_Gender, @p_Tel, @p_Mobile, @p_Address, @p_ZipCode, @p_IdCardNo, @p_HireDate, @p_Department, @p_LoginId, @p_RecordId out, @p_ErrMsg out, @p_RFlag out";

            SqlParameter[] parms = {
                    new SqlParameter("@p_Action", SqlDbType.Char, 1),
                    new SqlParameter("@p_EmployeeNo", SqlDbType.VarChar, 10),
                    new SqlParameter("@p_ChineseName", SqlDbType.VarChar, 20),
                    new SqlParameter("@p_EnglishName", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_Title", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_Gender", SqlDbType.VarChar, 2),
                    new SqlParameter("@p_Tel", SqlDbType.VarChar, 15), 
                    new SqlParameter("@p_Mobile", SqlDbType.VarChar, 15),
                    new SqlParameter("@p_Address", SqlDbType.VarChar, 200),
                    new SqlParameter("@p_ZipCode", SqlDbType.VarChar, 6), 
                    new SqlParameter("@p_IdCardNo", SqlDbType.VarChar, 18),
                    new SqlParameter("@p_HireDate", SqlDbType.DateTime),
                    new SqlParameter("@p_Department", SqlDbType.VarChar, 20),
                    new SqlParameter("@p_LoginId", SqlDbType.VarChar, 20),
                    new SqlParameter("@p_RecordId", SqlDbType.Int),
                    new SqlParameter("@p_ErrMsg", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RFlag", SqlDbType.SmallInt)};

            parms[0].Value = action;
            parms[1].Value = employee.EmployeeNo.Trim();
            parms[2].Value = employee.ChineseName.Trim();
            parms[3].Value = employee.EnglishName.Trim();
            parms[4].Value = employee.Title.Trim();
            parms[5].Value = employee.Gender.Trim();
            parms[6].Value = employee.Tel.Trim();
            parms[7].Value = employee.Mobile.Trim();
            parms[8].Value = employee.Address.Trim();
            parms[9].Value = employee.ZipCode.Trim();
            parms[10].Value = employee.IdCardNo.Trim();
            parms[11].Value = employee.HireDate;
            parms[12].Value = employee.Department.Trim();
            parms[13].Value = employee.LoginId.Trim();
            parms[14].Value = employee.RecordId;
            parms[14].Direction = ParameterDirection.InputOutput;
            parms[15].Value = "";
            parms[15].Direction = ParameterDirection.InputOutput;
            parms[16].Value = 0;
            parms[16].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, sql, parms);
            recordId = (int)parms[14].Value;
        }
    }
}
